package com.saturn.sldb;

import java.sql.Connection;
import java.util.List;
import java.util.Map;

import org.json.JSONObject;

import com.saturn.app.db.DymaticCondition;
import com.saturn.app.db.ListData;
import com.saturn.app.db.ORMapping;
import com.saturn.app.db.ResultORMapping;
import com.saturn.app.db.SimpleDaoTemplate;

public class Reconciliation {
	private String id;
	private String createTime;
	private String creater;
	private String department;
	private String state;
	private String subject; //付款科目
	private String total; //分配总额
	private String surplus; // 支出款项
	private String remark; // 备注
	private String createrName;
	private String balance; // 差额
	private String type; //救助类型  临时救助/一事一议
	
	private static ORMapping<Reconciliation> mapping = new ResultORMapping<Reconciliation>();

		public static int add(Reconciliation vo) {
			// 指定值对象类型(VOClass)。例子：User
			// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user(id, name, gender)
			// 根据列的顺序获取值对象的属性值。例子：vo.getId(), vo.getName(), vo.getGender()
			return SimpleDaoTemplate
					.update("INSERT INTO sldb_reconciliation("
							+ "createTime, creater, total, surplus," 
							+ "department, state, remark,subject, createrName, type) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?)",
							vo.createTime,vo.creater, vo.total, vo.surplus, 
							vo.department, vo.state,vo.remark, vo.subject, 
							vo.createrName, vo.type);
		}
		
		public static int edit(final Reconciliation vo) {
			return SimpleDaoTemplate.update("UPDATE sldb_reconciliation SET "
					+ "createTime=?, creater=?, total=?, surplus=?," 
					+ "department=?, state=?, remark=?,subject=?, "
					+ "createrName=?, balance=?, type=?"
							+ "WHERE id = ?",  vo.createTime,
							vo.creater,vo.total, vo.surplus, vo.department, vo.state,
							vo.remark,vo.subject, vo.createrName,vo.balance,vo.type, vo.id);
		}
		
		public static int editFiles(String id, String files) {
			// 指定值对象类型(VOClass)。例子：User
			// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
			// 指定修改列信息(modify)。例子：name=?, value=?
			// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
			return SimpleDaoTemplate
					.update("UPDATE sldb_Reconciliation SET "
							+ " files = ? "
							+ "WHERE id = ?",
							files, id);
		}

			public static Reconciliation get(String id) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT * FROM sldb_reconciliation WHERE 1 = 1 and id = '" + id + "'",
						null, mapping, Reconciliation.class);
			}
			public static Reconciliation getBycondition(String subject, String department, String type) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT * FROM sldb_reconciliation WHERE 1 = 1 and subject = '" + subject + "' and department = '"+department+"'and type = '"+type+"'",
						null, mapping, Reconciliation.class);
			}
			public static Reconciliation getByCreater(String creater) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT * FROM sldb_Reconciliation WHERE 1 = 1 and creater = '" + creater + "' order by id desc",
						null, mapping, Reconciliation.class);
			}
			
			public static List<Reconciliation> getAllPersonByIdcard(String identify) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return (List<Reconciliation>) SimpleDaoTemplate.query(
						"SELECT * FROM sldb_Reconciliation WHERE 1 = 1 and identify = '" + identify + "'",
						null, mapping, Reconciliation.class);
			}

			public static Reconciliation getIdWithTime(String createTime) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定表主键(key)。例子:key=id
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.queryOne(
						"SELECT id FROM sldb_temporary WHERE 1 = 1 and createTime = '"+ createTime +"'",
						null, mapping, Reconciliation.class);
			}
			
			public static ListData<Reconciliation> getAll(Reconciliation vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_reconciliation WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo,
								"department","createTime","subject", "type", "createrName")
								.addCondition("ORDER BY {0} {1}", orderBy,
								order), mapping, Reconciliation.class, start, offset);
			}
			

			public static ListData<Reconciliation> getAllWithOutCreater(Reconciliation vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"select distinct (s.pid), t.* from  sldb_Reconciliation t ," +
						"sldb_person_state s where 1 = 1 and t.id = s.pid",
						new DymaticCondition().addSimpleCondition(vo, "state",
								"identify", "name", "sex", "createrName",
								"createTime"), mapping, Reconciliation.class, start, offset);
			}
			
			public static ListData<Reconciliation> getAllWithoutCreater(Reconciliation vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate.query(
						"SELECT * FROM sldb_Reconciliation WHERE 1 = 1",
						new DymaticCondition().addSimpleCondition(vo, "state",
								"identify", "name", "gender", "createrName",
								"createTime","creater","numberNO").addCondition("ORDER BY {0} {1}", orderBy,
								order), mapping, Reconciliation.class, start, offset);
			}

			public static int replay(String id, String state) {
				return SimpleDaoTemplate.update(
						"UPDATE sldb_temporary SET state = ? where id = ?", state,id);
			}
			public static ListData<Reconciliation> getReconciliationStates(Reconciliation vo, String start,
					String offset, String orderBy, String order) {
				return SimpleDaoTemplate
						.query("select distinct (s.pid), p.* from  sldb_Reconciliation p ,sldb_person_state s where 1 = 1 and p.id = s.pid",
								new DymaticCondition().addCondition("and s.userId = '?' ", vo.getCreater()), mapping,
								Reconciliation.class, start, offset);
			}
			
			private static int state(Connection connection, String id, String state) {
				return SimpleDaoTemplate.update(connection,
						"UPDATE sldb_temporary SET state = ? WHERE id = ?", state, id);
			}

			public static  int UpdateState(String id, String state) {
				return SimpleDaoTemplate.update(
						"UPDATE sldb_temporary SET state = ? WHERE id = ?", state, id);
			}
			
			public static int remove(final String id) {
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				return SimpleDaoTemplate.update("DELETE FROM sldb_Reconciliation WHERE id = ?",
						id);
			}

			public static void removes(String[] ids) {
				if (ids != null) {
					for (String id : ids) {
						remove(id);
					}
				}
			}

			public static ListData<Reconciliation> getAllOldWithOutUserId(Reconciliation vo, String start,
					String offset, String orderBy, String order) {
				// 指定值对象类型(VOClass)。例子VOClass=User
				// 指定插入表名称(tableName)。例子：如user表，tableName=user
				// 指定O-R映射规则对象。默认mapping
				return SimpleDaoTemplate
						.query("select distinct (s.pid), p.* from  sldb_temporary p ,sldb_temporary_state s where 1 = 1 and p.id = s.pid",
								new DymaticCondition(), mapping,
								Reconciliation.class, start, offset);
			}
	//根据申报操作修改状态
	public static int editToState(Reconciliation vo) { 
		// 指定值对象类型(VOClass)。例子：User
		// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
		// 指定修改列信息(modify)。例子：name=?, value=?
		// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
		return SimpleDaoTemplate
				.update("UPDATE sldb_Reconciliation SET "
						+ "identify = ?, name = ?,sex = ?,home = ?,"
						+ "audit = ?, money = ?, files = ?,  photo = ?,createTime = ?, creater = ?, "
						+ "createrName = ?, createrDepartment = ?,state = ?,  inputType = ?," +
						" numberNO = ?, peopleType=?, homeNumber=?, remarks=?, applyCause=?, causeDesc=?," +
						"startCause=?, subject=?, accountType=?, registration=?, " +
						"safeWay=?, type=?" + "WHERE id = ?",
						vo.createTime,vo.creater, vo.state, vo.subject,vo.id);
	}
	//更新审批人
	public static int editToAudit(Reconciliation vo) { 
		// 指定值对象类型(VOClass)。例子：User
		// 指定插入表名称(tableName)。例子：如user表3个列，tableName=user
		// 指定修改列信息(modify)。例子：name=?, value=?
		// 根据修改列的顺序获取值对象的属性值。例子：vo.getName(), vo.getValue(), vo.getId()
		return SimpleDaoTemplate
				.update("UPDATE sldb_Reconciliation SET "
						+ "identify = ?, name = ?,sex = ?,home = ?,"
						+ "audit = ?, money = ?, files = ?,  photo = ?,createTime = ?, creater = ?, "
						+ "createrName = ?, createrDepartment = ?,state = ?,  inputType = ?," +
						" numberNO = ?, peopleType=?, homeNumber=?, remarks=?, applyCause=?, causeDesc=?," +
						"startCause=?, subject=?, accountType=?, registration=?, " +
						"safeWay=?, type=?" + "WHERE id = ?",
						vo.createTime,vo.creater, vo.state, vo.subject,vo.id);
	}
	
	public Reconciliation(String id, String createTime, String creater, String state,
			String department,String remark,String subject, String total, String surplus,
			String balance, String createrName, String type) {
		super();
		this.id = id;
		this.createTime = createTime;
		this.creater = creater;
		this.department = department;
		this.total = total;
		this.surplus = surplus;
		this.state = state;
		this.remark =remark;
		this.subject = subject;
		this.createrName = createrName;
		this.balance = balance;
		this.type = type;
		
	}

	public Reconciliation() {
		super();
	}

	public String getId() {
		return id;
	}

	public String getCreateTime() {
		return createTime;
	}

	public void setCreateTime(String createTime) {
		this.createTime = createTime;
	}

	public String getCreater() {
		return creater;
	}

	public void setCreater(String creater) {
		this.creater = creater;
	}

	public String getState() {
		return state;
	}

	public void setState(String state) {
		this.state = state;
	}
	public String getSubject() {
		return subject;
	}

	public void setSubject(String subject) {
		this.subject = subject;
	}

	public String getDepartment() {
		return department;
	}

	public void setDepartment(String department) {
		this.department = department;
	}

	public String getTotal() {
		return total;
	}

	public void setTotal(String total) {
		this.total = total;
	}

	public String getSurplus() {
		return surplus;
	}

	public void setSurplus(String surplus) {
		this.surplus = surplus;
	}

	public String getRemark() {
		return remark;
	}

	public void setRemark(String remark) {
		this.remark = remark;
	}

	public void setId(String id) {
		this.id = id;
	}

	public String getCreaterName() {
		return createrName;
	}

	public void setCreaterName(String createrName) {
		this.createrName = createrName;
	}

	public String getBalance() {
		return balance;
	}

	public void setBalance(String balance) {
		this.balance = balance;
	}

	
	public String getType() {
		return type;
	}

	public void setType(String type) {
		this.type = type;
	}

	@Override
	public String toString() {
		return new JSONObject(this).toString();
	}
	
	public int checkSafeWay(String identity){
		String sql = "select count(*) from `sldb_country2015-08` where identify='"+identity+"' union select count(*) from `sldb_city2015-08` where identify='"+identity+"'";
		int count = SimpleDaoTemplate.queryCount(sql, null);
		return count;
	}
	
	public String checkHomeNumber(String identity){
		String sql = "select number from `sldb_country2015-08` where identify='"+identity+"' union select number from `sldb_city2015-08` where identify='"+identity+"'";
		List<Map<String,String>> resultList = SimpleDaoTemplate.query(sql, null,new String[]{"number"});
		String homeNumber = "";
		if(resultList.size()!=0){
			homeNumber = resultList.get(0).get("number");
		}
		return homeNumber;
	}
}
